﻿// 微软官方出品的Office操作库 Open XML SDK
// 参考： https://www.thecodebuzz.com/read-excel-file-in-dotnet-core-2-1/
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataConverter
{
    internal class OpenXmlSdkDemo
    {
        public static void Test()
        {
            Console.WriteLine("Reading...");
            ReadExcelFile();
        }

        public static void ReadExcelFile(string xlsx_file = "Output.xlsx")
        {
            if (!File.Exists(xlsx_file))
            {
                Console.WriteLine($"Excel file '{xlsx_file}' does not exist.");
                return;
            }


            List<List<string>> list = new List<List<string>>();
            try
            {
                //Lets open the existing excel file and read through its content . Open the excel using openxml sdk
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(xlsx_file, false))
                {
                    //create the object for workbook part  
                    WorkbookPart workbookPart = doc.WorkbookPart;
                    Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
                    foreach (Sheet thesheet in thesheetcollection)
                    {
                        list.Add(new List<string>() { "$Sheet$:" + thesheet.Name});
                        Console.WriteLine($"------------- Excel Sheet: {thesheet.Name} ----------");
                        Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
                        SheetData thesheetdata = theWorksheet.GetFirstChild<SheetData>();
                        foreach (Row thecurrentrow in thesheetdata)
                        {
                            List<string> rowdata = new List<string>();
                            foreach (Cell thecurrentcell in thecurrentrow)
                            {
                                string currentcellvalue = GetCellValue(workbookPart, thecurrentcell);
                                rowdata.Add(currentcellvalue);
                            }
                            list.Add(rowdata);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }

            foreach (var row in list)
            {
                foreach (var item in row)
                    Console.Write($"{item}    ");
                Console.WriteLine();
            }
        }

        private static string GetCellValue(WorkbookPart workbookPart, Cell thecurrentcell)
        {
            //statement to take the integer value  
            string currentcellvalue = string.Empty;
            if (thecurrentcell.DataType != null)
            {
                if (thecurrentcell.DataType == CellValues.SharedString)
                {
                    if (Int32.TryParse(thecurrentcell.InnerText, out int id))
                    {
                        SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
                        if (item.Text != null)
                        {
                            //code to take the string value  
                            currentcellvalue = item.Text.Text;
                        }
                        else if (item.InnerText != null)
                        {
                            currentcellvalue = item.InnerText;
                        }
                        else if (item.InnerXml != null)
                        {
                            currentcellvalue = item.InnerXml;
                        }
                    }
                }
            }

            return currentcellvalue;
        }
    }
}
